package com.infodms.dms.dao.sales.planmanage;

import java.sql.ResultSet;
import java.util.HashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


import org.apache.log4j.Logger;

import com.infodms.dms.actions.sales.planmanage.PlanUtil.PlanUtil;
import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;
import com.infoservice.po3.core.callback.DAOCallback;

public class OemRequireForecastReportDao extends BaseDao<PO> {
	
	public static Logger logger = Logger.getLogger(YearPlanDao.class);
	private static final OemRequireForecastReportDao dao = new OemRequireForecastReportDao ();
	public static final OemRequireForecastReportDao getInstance() {
		return dao;
	}
	

	
	/*
	 * 区域查询SQL，SUB单指DEALE上一级ORG
	 */
	
	private String getSubForecastSql(String orgId,String areaId,List<Map<String, Object>> mapList,String companyId){
		StringBuffer sql=new StringBuffer("");


		sql.append("select Y.SERIES_CODE,\n");
		sql.append("       Y.SERIES_NAME,\n");  
		sql.append("       Y.MODEL_CODE,\n");  
		for(int i=0;i<mapList.size();i++){
			sql.append("       SUM(D"+i+") D"+i+",\n");  
			sql.append("       SUM(S"+i+") S"+i+",\n");  
		}
		sql.append("       Y.GROUP_ID,\n");  
		sql.append("       Y.MODEL_NAME\n");
		sql.append("from (\n");
		sql.append("select x.SERIES_CODE,\n");
		sql.append("       x.SERIES_NAME,\n");  
		sql.append("       x.MODEL_CODE,\n");  
		for(int i=0;i<mapList.size();i++){
		    Map<String, Object> map=mapList.get(i);
			sql.append("       case\n");  
			sql.append("         when x.FORECAST_YEAR = "+map.get("YEAR")+" and x.FORECAST_MONTH = "+map.get("MONTH")+" and x.ORG_TYPE="+Constant.ORG_TYPE_DEALER+" then\n");  
			sql.append("          x.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end D"+i+",\n");
			sql.append("       case\n");  
			sql.append("         when x.FORECAST_YEAR = "+map.get("YEAR")+" and x.FORECAST_MONTH = "+map.get("MONTH")+" and x.ORG_TYPE="+Constant.ORG_TYPE_OEM+" then\n");  
			sql.append("          x.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end S"+i+",\n"); 
		}
		sql.append("       x.GROUP_ID,\n");  
		sql.append("       x.MODEL_NAME\n");  
		sql.append("  from (select g.SERIES_CODE,\n");  
		sql.append("               g.SERIES_NAME,\n");  
		sql.append("               g.MODEL_CODE,\n");  
		sql.append("               g.GROUP_ID,\n");  
		sql.append("               g.MODEL_NAME,\n");  
		sql.append("               a.FORECAST_YEAR,\n");  
		sql.append("               a.FORECAST_MONTH,\n");  
		sql.append("               a.org_type,\n");  
		sql.append("               nvl(sum(a.FORECAST_AMOUNT), 0) amount\n");  
		sql.append("          from (select nvl(sum(d.FORECAST_AMOUNT),0) FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       f.FORECAST_ID,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");  
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
		sql.append("                   and f.DEALER_ID in\n");  
		sql.append("                       (select r.dealer_id\n");  
		sql.append("                          from TM_DEALER_ORG_RELATION r\n");  
		sql.append("                         where r.org_id = "+orgId+")\n");  
		sql.append("                group by d.GROUP_ID,f.FORECAST_ID, f.FORECAST_YEAR, f.ORG_TYPE, f.FORECAST_MONTH \n");
		sql.append("                union all\n");  
		sql.append("                select d.FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       f.FORECAST_ID,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");  
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_UNCONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("                   and f.ORG_ID = "+orgId+") a,\n");  
		sql.append("               (select g2.GROUP_ID,\n");  
		sql.append("                       g1.GROUP_CODE,\n");  
		sql.append("                       g1.GROUP_ID GROUP_ID1,\n");  
		sql.append("                       g3.GROUP_CODE SERIES_CODE,\n");  
		sql.append("                       g3.GROUP_NAME SERIES_NAME,\n");  
		sql.append("                       g2.GROUP_CODE MODEL_CODE,\n");  
		sql.append("                       g2.GROUP_NAME MODEL_NAME\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP g1,\n");  
		sql.append("                       TM_VHCL_MATERIAL_GROUP g2,\n");  
		sql.append("                       TM_VHCL_MATERIAL_GROUP g3\n");  
		sql.append("                 where g1.PARENT_GROUP_ID = g2.GROUP_ID\n");  
		sql.append("                   and g2.PARENT_GROUP_ID = g3.GROUP_ID\n");  
		sql.append("                   and g1.FORCAST_FLAG = 1\n");  
		sql.append("                   and g2.FORCAST_FLAG = 1\n");  
		sql.append("                   and g3.FORCAST_FLAG = 1\n");  
		sql.append("                   and g1.GROUP_LEVEL = 4\n");  
		sql.append("                   and g1.GROUP_ID in\n");  
		sql.append("                       (select T1.GROUP_ID\n");  
		sql.append("                          from tm_vhcl_material_group t1\n");  
		sql.append("                         WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                           and t1.GROUP_LEVEL = 4\n");  
		sql.append("                         start with t1.group_id IN\n");  
		sql.append("                                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                                       FROM tm_area_group tap\n");  
		sql.append("                                      where tap.area_id = "+areaId+")\n");  
		sql.append("                        connect by prior t1.group_id = t1.parent_group_id)) g\n");  
		sql.append("         where a.GROUP_ID(+) = g.GROUP_ID1\n");  
		sql.append("         group by g.SERIES_CODE,\n");  
		sql.append("                  g.SERIES_NAME,\n");  
		sql.append("                  g.MODEL_CODE,\n");  
		sql.append("                  g.GROUP_ID,\n");  
		sql.append("                  g.MODEL_NAME,\n");  
		sql.append("                  a.FORECAST_YEAR,\n");  
		sql.append("                  a.org_type,\n");  
		sql.append("                  a.FORECAST_MONTH) X\n");  
		sql.append(" order by x.model_code");
		sql.append(") Y\n");
		sql.append(" GROUP BY Y.SERIES_CODE,\n");  
		sql.append("       Y.SERIES_NAME,\n");  
		sql.append("       Y.MODEL_CODE,\n");  
		sql.append("       Y.GROUP_ID,\n");  
		sql.append("       Y.MODEL_NAME");
		sql.append("       ORDER BY Y.GROUP_ID");
		
		return sql.toString();
	}
	/*
	 * 总部查询SQL
	 */
	private String getDeptForecasetSql(String porgId,String orgId,String areaId,List<Map<String, Object>> mapList,String companyId){
		StringBuffer sql=new StringBuffer("");


		sql.append("select Y.SERIES_CODE,\n");
		sql.append("       Y.SERIES_NAME,\n");  
		sql.append("       Y.MODEL_CODE,\n");  
		for(int i=0;i<mapList.size();i++){
			sql.append("       SUM(D"+i+") D"+i+",\n");  
			sql.append("       SUM(S"+i+") S"+i+",\n");  
		}
		sql.append("       Y.GROUP_ID,\n");  
		sql.append("       Y.MODEL_NAME\n");
		sql.append("from (\n");
		sql.append("select x.SERIES_CODE,\n");
		sql.append("       x.SERIES_NAME,\n");  
		sql.append("       x.MODEL_CODE,\n");  
		for(int i=0;i<mapList.size();i++){
		    Map<String, Object> map=mapList.get(i);
			sql.append("       case\n");  
			sql.append("         when x.FORECAST_YEAR = "+map.get("YEAR")+" and x.FORECAST_MONTH = "+map.get("MONTH")+" and x.ORG_TYPE="+Constant.ORG_TYPE_OEM+" and x.duty='sub' then\n");  
			sql.append("          x.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end D"+i+",\n");
			sql.append("       case\n");  
			sql.append("         when x.FORECAST_YEAR = "+map.get("YEAR")+" and x.FORECAST_MONTH = "+map.get("MONTH")+" and x.ORG_TYPE="+Constant.ORG_TYPE_OEM+" and x.duty='dept' then\n");  
			sql.append("          x.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end S"+i+",\n"); 
		}
		sql.append("       x.GROUP_ID,\n");  
		sql.append("       x.MODEL_NAME\n");  
		sql.append("  from (select g.SERIES_CODE,\n");  
		sql.append("               g.SERIES_NAME,\n");  
		sql.append("               g.MODEL_CODE,\n");  
		sql.append("               g.GROUP_ID,\n");  
		sql.append("               g.MODEL_NAME,\n");  
		sql.append("               a.FORECAST_YEAR,\n");  
		sql.append("               a.FORECAST_MONTH,\n"); 
		sql.append("               a.duty,\n"); 
		sql.append("               a.org_type,\n");  
		sql.append("               nvl(sum(a.FORECAST_AMOUNT), 0) amount\n");  
		sql.append("          from (select nvl(sum(d.FORECAST_AMOUNT),0) FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       f.FORECAST_ID,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");
		sql.append("                       'sub' duty,\n");
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		/*sql.append("                   and f.ORG_ID in\n");  
		sql.append("                   (select org.ORG_ID\n");
		sql.append("                          from TM_ORG org\n");  
		sql.append("                           where org.duty_type="+Constant.DUTY_TYPE_LARGEREGION+"\n");
		sql.append("                           and org.status="+Constant.STATUS_ENABLE+"\n");
		sql.append("                           connect by prior org.ORG_ID = org.PARENT_ORG_ID\n");  
		sql.append("                           start with org.org_id = "+porgId+")\n");*/
		sql.append("                group by d.GROUP_ID,f.FORECAST_ID, f.FORECAST_YEAR, f.ORG_TYPE, f.FORECAST_MONTH \n");
		sql.append("                union all\n");  
		sql.append("                select d.FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       f.FORECAST_ID,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");
		sql.append("                       'dept' duty,\n");
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_UNCONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("                   and f.ORG_ID = "+orgId+") a,\n");  
		sql.append("               (select g2.GROUP_ID,\n");  
		sql.append("                       g1.GROUP_CODE,\n");  
		sql.append("                       g1.GROUP_ID GROUP_ID1,\n");  
		sql.append("                       g3.GROUP_CODE SERIES_CODE,\n");  
		sql.append("                       g3.GROUP_NAME SERIES_NAME,\n");  
		sql.append("                       g2.GROUP_CODE MODEL_CODE,\n");  
		sql.append("                       g2.GROUP_NAME MODEL_NAME\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP g1,\n");  
		sql.append("                       TM_VHCL_MATERIAL_GROUP g2,\n");  
		sql.append("                       TM_VHCL_MATERIAL_GROUP g3\n");  
		sql.append("                 where g1.PARENT_GROUP_ID = g2.GROUP_ID\n");  
		sql.append("                   and g2.PARENT_GROUP_ID = g3.GROUP_ID\n"); 
		sql.append("                   and g1.FORCAST_FLAG = 1\n");  
		sql.append("                   and g2.FORCAST_FLAG = 1\n");  
		sql.append("                   and g3.FORCAST_FLAG = 1\n");   
		sql.append("                   and g1.GROUP_LEVEL = 4\n");  
		sql.append("                   and g1.GROUP_ID in\n");  
		sql.append("                       (select T1.GROUP_ID\n");  
		sql.append("                          from tm_vhcl_material_group t1\n");  
		sql.append("                         WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                           and t1.GROUP_LEVEL = 4\n");  
		sql.append("                         start with t1.group_id IN\n");  
		sql.append("                                    (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                                       FROM tm_area_group tap\n");  
		sql.append("                                      where tap.area_id = "+areaId+")\n");  
		sql.append("                        connect by prior t1.group_id = t1.parent_group_id)) g\n");  
		sql.append("         where a.GROUP_ID(+) = g.GROUP_ID1\n");  
		sql.append("         group by g.SERIES_CODE,\n");  
		sql.append("                  g.SERIES_NAME,\n");  
		sql.append("                  g.MODEL_CODE,\n");  
		sql.append("                  g.GROUP_ID,\n");  
		sql.append("                  g.MODEL_NAME,\n");  
		sql.append("                  a.FORECAST_YEAR,\n");  
		sql.append("                  a.org_type,\n");  
		sql.append("                  a.duty,\n");
		sql.append("                  a.FORECAST_MONTH) X\n");  
		sql.append(" order by x.model_code");
		sql.append(") Y\n");
		sql.append(" GROUP BY Y.SERIES_CODE,\n");  
		sql.append("       Y.SERIES_NAME,\n");  
		sql.append("       Y.MODEL_CODE,\n");  
		sql.append("       Y.GROUP_ID,\n");  
		sql.append("       Y.MODEL_NAME");
		sql.append("       ORDER BY Y.GROUP_ID");
		
		return sql.toString();
	}
	
	/*
	 * 区域查询SQL，SUB单指DEALE上一级ORG
	 */
	
	private String getSubForecastSaveSearchSql(String pgroupId,String orgId,String areaId,List<Map<String, Object>> mapList,String companyId){
		StringBuffer sql=new StringBuffer();
		sql.append("select Y.GROUP_ID,\n");
		sql.append("       Y.group_name,\n");  
		for(int i=0;i<mapList.size();i++){
			sql.append("       SUM(D"+i+") D"+i+",\n");  
			sql.append("       NVL(SUM(S"+i+"),SUM(D"+i+")) S"+i+",\n");  
		}
		sql.append("       Y.group_code\n");
		sql.append("from (\n");
		sql.append("select aa.group_id GROUP_ID,\n");
		sql.append("       aa.group_name,\n");  
		for(int i=0;i<mapList.size();i++){
		    Map<String, Object> map=mapList.get(i);
			sql.append("       case\n");  
			sql.append("         when aa.FORECAST_YEAR = "+map.get("YEAR")+" and aa.FORECAST_MONTH = "+map.get("MONTH")+" and AA.ORG_TYPE="+Constant.ORG_TYPE_DEALER+" then\n");  
			sql.append("          aa.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end D"+i+",\n");
			
			sql.append("       case\n");  
			sql.append("         when aa.FORECAST_YEAR = "+map.get("YEAR")+" and aa.FORECAST_MONTH = "+map.get("MONTH")+" and AA.ORG_TYPE="+Constant.ORG_TYPE_OEM+" then\n");  
			sql.append("          aa.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          null\n");  
			sql.append("       end S"+i+",\n");
		}
		  
		sql.append("       aa.group_code\n");  
		sql.append("  from (\n");  
		sql.append("        select b.group_id,\n");  
		sql.append("                b.group_code,\n");  
		sql.append("                b.group_name,\n"); 
		sql.append("                a.org_type,\n");
		sql.append("                nvl(a.FORECAST_AMOUNT, 0) amount,\n");  
		sql.append("                a.FORECAST_YEAR,\n");  
		sql.append("                a.FORECAST_MONTH\n");  
		sql.append("          from (select  nvl(sum(d.FORECAST_AMOUNT),0) FORECAST_AMOUNT,\n");  
		sql.append("                        f.FORECAST_MONTH,\n");  
		sql.append("                        d.GROUP_ID,\n");
		sql.append("                        f.ORG_TYPE,\n"); 
		sql.append("                        f.FORECAST_YEAR\n");  
		sql.append("                   from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                        TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                  where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		sql.append("                   and f.DEALER_ID in\n");  
		sql.append("                       (select r.dealer_id\n");  
		sql.append("                          from TM_DEALER_ORG_RELATION r\n");  
		sql.append("                         where r.org_id = "+orgId+")\n");  
		sql.append("                group by d.GROUP_ID, f.FORECAST_YEAR, f.ORG_TYPE, f.FORECAST_MONTH \n");
		sql.append("                union all\n");  
		sql.append("                select d.FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");  
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n");  
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_UNCONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("                   and f.ORG_ID = "+orgId+") a,\n"); 
		
		sql.append("                (select T1.GROUP_ID, t1.GROUP_CODE, t1.group_name\n");  
		sql.append("                   from tm_vhcl_material_group t1\n");  
		sql.append("                  WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                    and t1.GROUP_LEVEL = 4\n");  
		sql.append("                    and t1.FORCAST_FLAG = 1\n");  
		sql.append("                    and t1.PARENT_GROUP_ID = "+pgroupId+"\n");  
		sql.append("                  start with t1.group_id IN\n");  
		sql.append("                             (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                                FROM tm_area_group tap\n");  
		sql.append("                               where tap.area_id = "+areaId+")\n");  
		sql.append("                 connect by prior t1.group_id = t1.parent_group_id) b\n");  
		sql.append("         where a.group_id(+) = b.group_id) aa\n");

		sql.append(") Y\n");
		sql.append(" GROUP BY Y.GROUP_ID,\n");  
		sql.append("       Y.GROUP_NAME,\n");  
		sql.append("       Y.GROUP_CODE\n");  
		sql.append("       ORDER BY Y.GROUP_CODE");
		
		return sql.toString();
	}
	/*
	 * 区域查询SQL，SUB单指DEALE上一级ORG
	 */
	
	private String getDeptForecastSaveSearchSql(String porgId,String pgroupId,String orgId,String areaId,List<Map<String, Object>> mapList,String companyId){
		StringBuffer sql=new StringBuffer();
		sql.append("select Y.GROUP_ID,\n");
		sql.append("       Y.group_name,\n");  
		for(int i=0;i<mapList.size();i++){
			sql.append("       SUM(D"+i+") D"+i+",\n");  
			sql.append("       NVL(SUM(S"+i+"),SUM(D"+i+")) S"+i+",\n");  
		}
		sql.append("       Y.group_code\n");
		sql.append("from (\n");
		sql.append("select aa.group_id GROUP_ID,\n");
		sql.append("       aa.group_name,\n");  
		for(int i=0;i<mapList.size();i++){
		    Map<String, Object> map=mapList.get(i);
			sql.append("       case\n");  
			sql.append("         when aa.FORECAST_YEAR = "+map.get("YEAR")+" and aa.FORECAST_MONTH = "+map.get("MONTH")+" and AA.ORG_TYPE="+Constant.ORG_TYPE_OEM+" and aa.duty='sub' then\n");  
			sql.append("          aa.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          0\n");  
			sql.append("       end D"+i+",\n");
			
			sql.append("       case\n");  
			sql.append("         when aa.FORECAST_YEAR = "+map.get("YEAR")+" and aa.FORECAST_MONTH = "+map.get("MONTH")+" and AA.ORG_TYPE="+Constant.ORG_TYPE_OEM+" and aa.duty='dept' then\n");  
			sql.append("          aa.AMOUNT\n");  
			sql.append("         else\n");  
			sql.append("          null\n");  
			sql.append("       end S"+i+",\n");
		}
		  
		sql.append("       aa.group_code\n");  
		sql.append("  from (\n");  
		sql.append("        select b.group_id,\n");  
		sql.append("                b.group_code,\n");  
		sql.append("                b.group_name,\n"); 
		sql.append("                a.org_type,\n");
		sql.append("                nvl(a.FORECAST_AMOUNT, 0) amount,\n");  
		sql.append("                a.FORECAST_YEAR,\n");  
		sql.append("                a.duty,\n");
		sql.append("                a.FORECAST_MONTH\n");  
		sql.append("          from (select  nvl(sum(d.FORECAST_AMOUNT),0) FORECAST_AMOUNT,\n");  
		sql.append("                        f.FORECAST_MONTH,\n");  
		sql.append("                        d.GROUP_ID,\n");
		sql.append("                        f.ORG_TYPE,\n"); 
		sql.append("                        'sub' duty,\n");
		sql.append("                        f.FORECAST_YEAR\n");  
		sql.append("                   from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                        TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                  where f.FORECAST_ID = d.FORECAST_ID\n"); 
		sql.append("                    and f.COMPANY_ID="+companyId+"\n"); 
		sql.append("                    and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		sql.append("                    and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n"); 
		/*sql.append("                    and f.ORG_ID in\n");  
		sql.append("                    (select org.ORG_ID\n");
		sql.append("                          from TM_ORG org\n");  
		sql.append("                           where org.duty_type="+Constant.DUTY_TYPE_LARGEREGION+"\n");
		sql.append("                           and org.status="+Constant.STATUS_ENABLE+"\n");
		sql.append("                           connect by prior org.ORG_ID = org.PARENT_ORG_ID\n");  
		sql.append("                           start with org.org_id = "+porgId+")\n");*/
		sql.append("                group by d.GROUP_ID, f.FORECAST_YEAR, f.ORG_TYPE, f.FORECAST_MONTH \n");
		sql.append("                union all\n");  
		sql.append("                select d.FORECAST_AMOUNT,\n");  
		sql.append("                       f.FORECAST_MONTH,\n");  
		sql.append("                       d.GROUP_ID,\n");  
		sql.append("                       f.ORG_TYPE,\n");  
		sql.append("                       'dept' duty,\n");
		sql.append("                       f.FORECAST_YEAR\n");  
		sql.append("                  from TT_VS_MONTHLY_FORECAST        f,\n");  
		sql.append("                       TT_VS_MONTHLY_FORECAST_DETAIL d\n");  
		sql.append("                 where f.FORECAST_ID = d.FORECAST_ID\n"); 
		sql.append("                   and f.COMPANY_ID="+companyId+"\n");
		sql.append("                   and f.STATUS = "+Constant.FORECAST_STATUS_UNCONFIRM+"\n");  
		sql.append("                   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("                   and f.ORG_ID = "+orgId+") a,\n"); 
		
		sql.append("                (select T1.GROUP_ID, t1.GROUP_CODE, t1.group_name\n");  
		sql.append("                   from tm_vhcl_material_group t1\n");  
		sql.append("                  WHERE T1.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                    and t1.GROUP_LEVEL = 4\n");  
		sql.append("                    and t1.FORCAST_FLAG = 1\n");  
		sql.append("                    and t1.PARENT_GROUP_ID = "+pgroupId+"\n");  
		sql.append("                  start with t1.group_id IN\n");  
		sql.append("                             (SELECT TAP.MATERIAL_GROUP_ID\n");  
		sql.append("                                FROM tm_area_group tap\n");  
		sql.append("                               where tap.area_id = "+areaId+")\n");  
		sql.append("                 connect by prior t1.group_id = t1.parent_group_id) b\n");  
		sql.append("         where a.group_id(+) = b.group_id) aa\n");

		sql.append(") Y\n");
		sql.append(" GROUP BY Y.GROUP_ID,\n");  
		sql.append("       Y.GROUP_NAME,\n");  
		sql.append("       Y.GROUP_CODE\n");  
		sql.append("       ORDER BY Y.GROUP_CODE");
		
		return sql.toString();
	}

	/*
	 * 组织查询预测信息，需根据参数计算查询哪些月份的数据
	 * orgLevel:dept部门，sub经销商上级
	 */
	public List<Map<String, Object>> selectOemRequireForecastModelInfo(String porgId,String orgId,String areaId,List<Map<String, Object>> mapList,String orgLevel,String companyId){
		List<Object> params = new LinkedList<Object>();
		String sql="";
		if("sub".equals(orgLevel)){
			sql=getSubForecastSql(orgId, areaId, mapList,companyId);
		}else{
			sql=getDeptForecasetSql(porgId,orgId, areaId, mapList,companyId);
		}
		return dao.pageQuery(sql, params, getFunName());
	}
	
	/*
	 * 经销可预测操作的数据
	 * 查询 配置ID NAME 月预测数量
	 */
		public List<Map<String, Object>> selectOemForecastOpeList(String porgId,String pgroupId,String orgId,String areaId,List<Map<String, Object>> mapList,String orgLevel,String companyId){
			List<Object> params = new LinkedList<Object>();
			String sql="";
			if("sub".equals(orgLevel)){
				sql=getSubForecastSaveSearchSql(pgroupId, orgId, areaId, mapList,companyId);
			}else{
				sql=getDeptForecastSaveSearchSql(porgId,pgroupId, orgId, areaId, mapList,companyId);
			}
			List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
			return list;
		}
		/*
		 * 查询需要清除的组织预测信息
		 * bl预测的第一个月的信息必须未确认状态的。其他月份不分状态清掉，到下一个月时候可再预测，当前月就会变成历史数据
		 */
		public List<Map<String, Object>> selectClrOemForecast(String year,String month,String areaId,String orgId,String groupId,String companyId,boolean bl){
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer("");

			sql.append("select f.FORECAST_ID,d.detail_id\n");
			sql.append("  from TT_VS_MONTHLY_FORECAST_DETAIL d, TT_VS_MONTHLY_FORECAST f\n");  
			sql.append(" where d.FORECAST_ID = f.FORECAST_ID\n");  
			sql.append("   and f.FORECAST_YEAR = "+year+"\n");  
			sql.append("   and f.FORECAST_MONTH = "+month+"\n");  
			if(bl){
				sql.append("   and f.STATUS = "+Constant.FORECAST_STATUS_UNCONFIRM+"\n");  
			}
			sql.append("   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n"); 
			sql.append("   and f.COMPANY_ID = "+companyId+"\n");
			sql.append("   and f.AREA_ID = "+areaId+"\n");  
			sql.append("   and f.ORG_ID = "+orgId+"\n");  
			if(groupId != null && !groupId.equals("")) 
				sql.append("   and d.GROUP_ID ="+groupId);

			return dao.pageQuery(sql.toString(), params, getFunName());
		}	
		
		/*
		 * 区域 查询未提报组织
		 */
		public PageResult<Map<String, Object>> selectSubRequireForecastUnreportedDealers(String orgId,String areaId,String companyId,List<Map<String, Object>> mapList,int curPage, int pageSize){
			List<Object> params = new LinkedList<Object>();
			 String sql=selectSubRequireForecastUnreportedDealersSQL(orgId, areaId,companyId, mapList);
			PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
			return ps;
		}
		public List<Map<String, Object>> downLoadSubRequireForecastUnreportedDealers(String orgId,String areaId,String companyId,List<Map<String, Object>> mapList){
			List<Object> params = new LinkedList<Object>();
            String sql=selectSubRequireForecastUnreportedDealersSQL(orgId, areaId,companyId, mapList);
            List<Map<String, Object>> list = pageQuery(sql.toString(), null, getFunName());
			return list;
		}
		public String  selectSubRequireForecastUnreportedDealersSQL(String orgId,String areaId,String companyId,List<Map<String, Object>> mapList){
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer("");

			for(int i=0;i<mapList.size();i++){
				Map<String, Object> map=mapList.get(i);

				sql.append("select td.DEALER_CODE,\n");
				sql.append("       td.DEALER_SHORTNAME,\n");  
				sql.append("       TMO.ORG_NAME,\n");  
				sql.append("       td.PHONE,\n");  
				sql.append("       td.LINK_MAN,\n");  
				sql.append("       '"+map.get("YEAR")+"年'||'"+map.get("MONTH")+"月' FORECAST_MONTH\n");  
				sql.append("  from TM_DEALER td, TM_DEALER_BUSINESS_AREA r, TM_DEALER_ORG_RELATION TDOR, TM_ORG TMO\n");  
				sql.append(" where td.DEALER_ID = r.DEALER_ID\n");
				sql.append("   and TD.DEALER_ID = TDOR.DEALER_ID\n");  
				sql.append("   and TDOR.ORG_ID = TMO.ORG_ID\n");  
				sql.append("   and td.OEM_COMPANY_ID="+companyId+"\n");
				sql.append("   and td.DEALER_TYPE IN ("+Constant.DEALER_TYPE_DVS+", "+Constant.DEALER_TYPE_QYZDL+")\n");  
				sql.append("   and r.AREA_ID = "+areaId+"\n");  
				sql.append("   and td.DEALER_ID in\n");  
				sql.append("       (select r.dealer_id from TM_DEALER_ORG_RELATION r where r.org_id = "+orgId+")\n");  
				sql.append("   and td.DEALER_ID not in (select f.DEALER_ID\n");  
				sql.append("                              from TT_VS_MONTHLY_FORECAST f\n");  
				sql.append("                             where f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("                               and f.AREA_ID = "+areaId+"\n");  
				sql.append("                               and f.FORECAST_YEAR = "+map.get("YEAR")+"\n");  
				sql.append("                               and f.FORECAST_MONTH = "+map.get("MONTH")+"\n");  
				sql.append("                               and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+")\n");
				sql.append(" union \n");
			}
            sql.delete(sql.lastIndexOf("union"),sql.length());
            
            return sql.toString();
		}
		
		/*
		 * 总部查询未提报组织名单，目前只有大区的情况
		 * orgId是车厂公司的ORGID
		 */
		public PageResult<Map<String, Object>> selectDeptRequireForecastUnreportedDealers(String orgId,String areaId,List<Map<String, Object>> mapList,int curPage, int pageSize){
			List<Object> params = new LinkedList<Object>();
			
			String sql=selectDeptRequireForecastUnreportedDealersSql(orgId, areaId, mapList);
			
			PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
			return ps;
		}
		/*
		 * 总部下载未提报组织名单，目前只有大区的情况
		 * orgId是车厂公司的ORGID
		 */
		public List<Map<String, Object>> downLoadDeptRequireForecastUnreportedDealers(String orgId,String areaId,List<Map<String, Object>> mapList){
			String sql=selectDeptRequireForecastUnreportedDealersSql(orgId, areaId, mapList);
			return dao.pageQuery(sql, null, getFunName());
		}
		/*
		 * 总部下载未提报组织名单，目前只有大区的情况SQL
		 * orgId是车厂公司的ORGID
		 */
		public String selectDeptRequireForecastUnreportedDealersSql(String orgId,String areaId,List<Map<String, Object>> mapList){
			StringBuffer sql=new StringBuffer("");

			for(int i=0;i<mapList.size();i++){
				Map<String, Object> map=mapList.get(i);

				sql.append("select r.DEALER_ID,\n");
				sql.append("       org.org_name,\n");  
				sql.append("       td.DEALER_CODE,\n");  
				sql.append("       td.DEALER_SHORTNAME,\n");  
				sql.append("       td.PHONE,\n");  
				sql.append("       td.LINK_MAN,\n");  
				sql.append("       '"+map.get("YEAR")+"年'||'"+map.get("MONTH")+"月' FORECAST_MONTH\n");  
				sql.append("  from TM_DEALER_ORG_RELATION r,\n");  
				sql.append("       TM_ORG                 org,\n");  
				sql.append("       TM_DEALER_BUSINESS_AREA   a,\n");  
				sql.append("       TM_DEALER              td\n");  
				sql.append(" where r.ORG_ID = org.ORG_ID\n");  
				sql.append("   and td.DEALER_ID = r.DEALER_ID\n");  
				sql.append("   and td.DEALER_TYPE = "+Constant.DEALER_TYPE_DVS+"\n");  
				sql.append("   and TD.DEALER_ID = a.DEALER_ID\n");  
				sql.append("   and org.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
				sql.append("   and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
				sql.append("   and org.PARENT_ORG_ID = "+orgId+"\n");  

				/*如果分大区小区时用这个
				 * sql.append("and org.ORG_ID in\n");
				sql.append("      (select org1.org_id\n");  
				sql.append("         from tm_org org1\n");  
				sql.append("        where org1.TREE_CODE like\n");  
				sql.append("              (select og.tree_code\n");  
				sql.append("                 from tm_org og\n");  
				sql.append("                where og.ORG_ID = 2010010100070674) || '%'\n");  
				sql.append("        and org1.DUTY_TYPE not in (10431001, 10431002, 10431005)\n");  
				sql.append("      )");*/

				
				sql.append("   and org.STATUS = "+Constant.STATUS_ENABLE+"\n");  
				sql.append("   and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
				sql.append("   and a.AREA_ID = "+areaId+"\n");  
				sql.append("   and td.DEALER_ID not in (select f.DEALER_ID\n");  
				sql.append("                              from TT_VS_MONTHLY_FORECAST f\n");  
				sql.append("                             where f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("                               and f.AREA_ID = "+areaId+"\n");  
				sql.append("                               and f.FORECAST_YEAR = "+map.get("YEAR")+"\n");  
				sql.append("                               and f.FORECAST_MONTH = "+map.get("MONTH")+"\n");  
				sql.append("                               and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+")");
				sql.append("   order by org.org_name\n"); 
				sql.append(" union \n");
			}
            sql.delete(sql.lastIndexOf("union"),sql.length());
            
            return sql.toString();
		}
		/*
		 * 总部查询未提报组织
		 */
		public PageResult<Map<String, Object>> selectDeptForecastLessThanMonthPlanDealers(String orgId,String areaId,List<Map<String, Object>> mapList,int curPage, int pageSize){
			List<Object> params = new LinkedList<Object>();
			String sql=selectDeptForecastLessThanMonthPlanDealersSql(orgId, areaId, mapList);
			PageResult<Map<String, Object>> ps = pageQuery(sql, null, getFunName(), pageSize, curPage);
			return ps;
		}
		/*
		 * 总部查询未提报组织下载
		 */
		public List<Map<String, Object>> downLoadDeptForecastLessThanMonthPlanDealers(String orgId,String areaId,List<Map<String, Object>> mapList){
			//List<Object> params = new LinkedList<Object>();
			String sql=selectDeptForecastLessThanMonthPlanDealersSql(orgId, areaId, mapList);
			List<Map<String, Object>> list = pageQuery(sql, null, getFunName());
			return list;
		}
		/*
		 * 总部查询未提报组织sql
		 */
		private String selectDeptForecastLessThanMonthPlanDealersSql(String orgId,String areaId,List<Map<String, Object>> mapList){
			StringBuffer sql=new StringBuffer("");
			
			for(int i=0;i<mapList.size();i++){
				Map<String, Object> map=mapList.get(i);

				sql.append("select a.DEALER_CODE,\n");
				sql.append("       a.DEALER_SHORTNAME,\n");  
				sql.append("       a.LINK_MAN,\n");  
				sql.append("       '"+map.get("YEAR")+"年'||'"+map.get("MONTH")+"月' FORECAST_MONTH,\n");  
				sql.append("       a.PHONE,\n");  
				sql.append("       a.SALE_AMOUNT,\n");  
				sql.append("       b.AMOUNT\n");  
				sql.append("  from (select tp.DEALER_ID,\n");  
				sql.append("               tpd.MATERIAL_GROUPID,\n");  
				sql.append("               tpd.SALE_AMOUNT,\n");  
				sql.append("               td.DEALER_CODE,\n");  
				sql.append("               td.DEALER_SHORTNAME,\n");  
				sql.append("               td.LINK_MAN,\n");  
				sql.append("               td.PHONE\n");  
				sql.append("          from TT_VS_MONTHLY_PLAN        tp,\n");  
				sql.append("               TT_VS_MONTHLY_PLAN_DETAIL tpd,\n");  
				sql.append("               TM_DEALER                 td\n");  
				sql.append("         where tp.PLAN_ID = tpd.PLAN_ID\n");  
				sql.append("           and td.DEALER_ID = tp.DEALER_ID\n");  
				sql.append("           and tp.PLAN_YEAR = "+map.get("YEAR")+"\n");  
				sql.append("           and tp.PLAN_MONTH = "+map.get("MONTH")+"\n");  
				sql.append("           and tp.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("           and tp.AREA_ID = "+areaId+"\n");  
				sql.append("           and tp.STATUS = "+Constant.PLAN_MANAGE_CONFIRM+"\n");  
				sql.append("           and tp.PLAN_VER = (select nvl(max(plan_ver), 0) plan_ver\n");  
				sql.append("                                from TT_VS_MONTHLY_PLAN\n");  
				sql.append("                               where plan_year = "+map.get("YEAR")+"\n");  
				sql.append("                                 and plan_month = "+map.get("MONTH")+"\n");  
				sql.append("                                 and AREA_ID = "+areaId+"\n");  
				sql.append("                                 and STATUS = "+Constant.PLAN_MANAGE_CONFIRM+"\n");  
				sql.append("                                 and ORG_TYPE = "+Constant.ORG_TYPE_DEALER+")) a,\n");  
				sql.append("       (select tf.DEALER_ID,\n");  
				sql.append("               tf.FORECAST_YEAR,\n");  
				sql.append("               tf.FORECAST_MONTH,\n");  
				sql.append("               tvmg2.GROUP_ID,\n");  
				sql.append("               sum(tfd.FORECAST_amount) AMOUNT\n");  
				sql.append("          from TT_VS_MONTHLY_FORECAST        tf,\n");  
				sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL tfd,\n");  
				sql.append("               TM_VHCL_MATERIAL_GROUP        tvmg1,\n");  
				sql.append("               TM_VHCL_MATERIAL_GROUP        tvmg2\n");  
				sql.append("         where tf.FORECAST_ID = tfd.FORECAST_ID\n");  
				sql.append("           and tfd.GROUP_ID = tvmg1.GROUP_ID\n");  
				sql.append("           and tf.FORECAST_year = "+map.get("YEAR")+"\n");  
				sql.append("           and tf.forecast_month = "+map.get("MONTH")+"\n");  
				sql.append("           and tf.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("           and tvmg2.TREE_CODE = substr(tvmg1.TREE_CODE, 0, 6)\n");  
				sql.append("           and tf.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
				sql.append("           and tf.AREA_ID = "+areaId+"\n");  
				sql.append("         group by tf.DEALER_ID,\n");  
				sql.append("                  tf.FORECAST_YEAR,\n");  
				sql.append("                  tf.FORECAST_MONTH,\n");  
				sql.append("                  tvmg2.GROUP_ID) b\n");  
				sql.append(" where a.DEALER_ID = b.dealer_id\n");  
				sql.append("   and a.MATERIAL_GROUPID = b.group_id\n");  
				sql.append("   and a.sale_amount > b.amount");
				sql.append(" union \n");
			}
			sql.delete(sql.lastIndexOf("union"),sql.length());
			return sql.toString();
		}
		/*
		 * 区域预测数量小月度目标下载
		 */
		public List<Map<String, Object>> downLoadSubForecastLessThanMonthPlanDealers(String orgId,String areaId,List<Map<String, Object>> mapList){
			String sql=selectSubForecastLessThanMonthPlanDealersSql(orgId, areaId, mapList);
			List<Map<String, Object>> list=dao.pageQuery(sql, null, getFunName());
			return list;
		}
		/*
		 * 区域预测数量小月度目标
		 */
		public PageResult<Map<String, Object>> selectSubForecastLessThanMonthPlanDealers(String orgId,String areaId,List<Map<String, Object>> mapList,int curPage, int pageSize){
			String sql=selectSubForecastLessThanMonthPlanDealersSql(orgId, areaId, mapList);
			PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
			return ps;
		}
		/*
		 * 区域预测数量小月度目标SQL
		 */
		public String selectSubForecastLessThanMonthPlanDealersSql(String orgId,String areaId,List<Map<String, Object>> mapList){
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer("");
			
			for(int i=0;i<mapList.size();i++){
				Map<String, Object> map=mapList.get(i);

				sql.append("select a.DEALER_CODE,\n");
				sql.append("       a.DEALER_SHORTNAME,\n");  
				sql.append("       a.LINK_MAN,\n");  
				sql.append("       '"+map.get("YEAR")+"年'||'"+map.get("MONTH")+"月' FORECAST_MONTH,\n");  
				sql.append("       a.PHONE,\n");  
				sql.append("       a.SALE_AMOUNT,\n");  
				sql.append("       b.AMOUNT\n");  
				sql.append("  from (select tp.DEALER_ID,\n");  
				sql.append("               tpd.MATERIAL_GROUPID,\n");  
				sql.append("               tpd.SALE_AMOUNT,\n");  
				sql.append("               td.DEALER_CODE,\n");  
				sql.append("               td.DEALER_SHORTNAME,\n");  
				sql.append("               td.LINK_MAN,\n");  
				sql.append("               td.PHONE\n");  
				sql.append("          from TT_VS_MONTHLY_PLAN        tp,\n");  
				sql.append("               TT_VS_MONTHLY_PLAN_DETAIL tpd,\n");  
				sql.append("               TM_DEALER                 td\n");  
				sql.append("         where tp.PLAN_ID = tpd.PLAN_ID\n");  
				sql.append("           and td.DEALER_ID = tp.DEALER_ID\n");

				sql.append("           and td.dealer_id in (select dealer_id\n");
				sql.append("                                  from TM_DEALER_ORG_RELATION r\n");  
				sql.append("                                 where r.ORG_ID ="+orgId+")\n");

				sql.append("           and tp.PLAN_YEAR = "+map.get("YEAR")+"\n");  
				sql.append("           and tp.PLAN_MONTH = "+map.get("MONTH")+"\n");  
				sql.append("           and tp.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("           and tp.AREA_ID = "+areaId+"\n");  
				sql.append("           and tp.STATUS = "+Constant.PLAN_MANAGE_CONFIRM+"\n");  
				sql.append("           and tp.PLAN_VER = (select nvl(max(plan_ver), 0) plan_ver\n");  
				sql.append("                                from TT_VS_MONTHLY_PLAN\n");  
				sql.append("                               where plan_year = "+map.get("YEAR")+"\n");  
				sql.append("                                 and plan_month = "+map.get("MONTH")+"\n");  
				sql.append("                                 and AREA_ID = "+areaId+"\n");  
				sql.append("                                 and STATUS = "+Constant.PLAN_MANAGE_CONFIRM+"\n");  
				sql.append("                                 and ORG_TYPE = "+Constant.ORG_TYPE_DEALER+")) a,\n");  
				sql.append("       (select tf.DEALER_ID,\n");  
				sql.append("               tf.FORECAST_YEAR,\n");  
				sql.append("               tf.FORECAST_MONTH,\n");  
				sql.append("               tvmg2.GROUP_ID,\n");  
				sql.append("               sum(tfd.FORECAST_amount) AMOUNT\n");  
				sql.append("          from TT_VS_MONTHLY_FORECAST        tf,\n");  
				sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL tfd,\n");  
				sql.append("               TM_VHCL_MATERIAL_GROUP        tvmg1,\n");  
				sql.append("               TM_VHCL_MATERIAL_GROUP        tvmg2\n");  
				sql.append("         where tf.FORECAST_ID = tfd.FORECAST_ID\n");  
				sql.append("           and tfd.GROUP_ID = tvmg1.GROUP_ID\n");  
				sql.append("           and tf.FORECAST_year = "+map.get("YEAR")+"\n");  
				sql.append("           and tf.forecast_month = "+map.get("MONTH")+"\n");  
				sql.append("           and tf.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
				sql.append("           and tvmg2.TREE_CODE = substr(tvmg1.TREE_CODE, 0, 6)\n");  
				sql.append("           and tf.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
				sql.append("           and tf.AREA_ID = "+areaId+"\n");  
				sql.append("         group by tf.DEALER_ID,\n");  
				sql.append("                  tf.FORECAST_YEAR,\n");  
				sql.append("                  tf.FORECAST_MONTH,\n");  
				sql.append("                  tvmg2.GROUP_ID) b\n");  
				sql.append(" where a.DEALER_ID = b.dealer_id\n");  
				sql.append("   and a.MATERIAL_GROUPID = b.group_id\n");  
				sql.append("   and a.sale_amount > b.amount");
				sql.append(" union \n");
			}
			sql.delete(sql.lastIndexOf("union"),sql.length());
			
			return sql.toString();
		}
		/*
		 * 组织明细
		 */
		public PageResult<Map<String, Object>> oemSelectOrgRequireForecast(Map<String, Object> map,int pageSize,int curPage){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String orgCode=(String)map.get("orgCode");
			String logonOrgType=(String)map.get("logonOrgType");
			String logonOrgId=(String)map.get("logonOrgId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("select org.ORG_CODE,\n");
			sql.append("       org.ORG_NAME,\n");  
			sql.append("       f.FORECAST_YEAR || '-' || f.FORECAST_MONTH FORECAST_MONTH,\n");  
			sql.append("       g.GROUP_CODE,\n");  
			sql.append("       g.GROUP_NAME,\n");  
			sql.append("       d.GROUP_ID,\n");  
			sql.append("       sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("  from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("       TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("       TM_ORG                        org,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP        g\n");  
			sql.append(" where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("   and f.ORG_ID = org.ORG_ID\n");  
			sql.append("   and g.GROUP_ID = d.GROUP_ID\n");  
			sql.append("      -- and org.DUTY_TYPE = 10431003\n");  
			sql.append("   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
				sql.append("      and f.ORG_ID=?\n");
				params.add(logonOrgId);
			}
			sql.append("   and f.AREA_ID = ?\n");  
			params.add(areaId);
			sql.append("   and f.COMPANY_ID = ?\n");  
			params.add(companyId);
			sql.append("   and f.FORECAST_YEAR = ?\n");  
			params.add(plan_year);
			sql.append("   and f.FORECAST_MONTH = ?\n");  
			params.add(plan_month);
			sql.append("   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			if(null!=orgCode&&!"".equals(orgCode)){
				sql.append("           and org.org_code in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			}
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and g.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" group by d.GROUP_ID,\n");  
			sql.append("          org.ORG_CODE,\n");  
			sql.append("          org.ORG_NAME,\n");  
			sql.append("          f.FORECAST_YEAR,\n");  
			sql.append("          f.FORECAST_MONTH,\n");  
			sql.append("          g.GROUP_CODE,\n");  
			sql.append("          g.GROUP_NAME\n");  
			sql.append(" order by org.ORG_CODE, d.GROUP_ID");

			return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		}
		
		/*
		 * dealer明细
		 */
		public PageResult<Map<String, Object>> oemSelectDealerRequireForecast(Map<String, Object> map,int pageSize,int curPage){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String dealerCode=(String)map.get("dealerCode");
			String logonOrgType=(String)map.get("logonOrgType");
			String logonOrgId=(String)map.get("logonOrgId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("select td.DEALER_CODE,\n");
			sql.append("       td.DEALER_SHORTNAME,\n");  
			sql.append("       f.FORECAST_YEAR || '-' || f.FORECAST_MONTH FORECAST_MONTH,\n");  
			sql.append("       g.GROUP_CODE,\n");  
			sql.append("       g.GROUP_NAME,\n");  
			sql.append("       d.GROUP_ID,\n");  
			sql.append("       sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("  from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("       TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("       TM_DEALER                       td,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP        g\n");  
			sql.append(" where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("   and f.DEALER_ID = td.DEALER_ID\n");  
			sql.append("   and g.GROUP_ID = d.GROUP_ID\n");  
			sql.append("   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
				sql.append("      and f.DEALER_ID in (select r.dealer_id\n");
				sql.append("                                    from TM_DEALER_ORG_RELATION r\n");  
				sql.append("                                   where r.ORG_ID = ?)\n");
				params.add(logonOrgId);
			}
			sql.append("   and f.AREA_ID = ?\n");  
			params.add(areaId);
			sql.append("   and f.COMPANY_ID = ?\n");  
			params.add(companyId);
			sql.append("   and f.FORECAST_YEAR = ?\n");  
			params.add(plan_year);
			sql.append("   and f.FORECAST_MONTH = ?\n");  
			params.add(plan_month);
			sql.append("   and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
			if(null!=dealerCode&&!"".equals(dealerCode)){
				sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			}
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and g.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" group by d.GROUP_ID,\n");  
			sql.append("          td.DEALER_CODE,\n");  
			sql.append("          td.DEALER_SHORTNAME,\n");  
			sql.append("          f.FORECAST_YEAR,\n");  
			sql.append("          f.FORECAST_MONTH,\n");  
			sql.append("          g.GROUP_CODE,\n");  
			sql.append("          g.GROUP_NAME\n");  
			sql.append(" order by td.DEALER_CODE, d.GROUP_ID");

			return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		}
		
		/*
		 *部门汇总
		 */
		public PageResult<Map<String, Object>> oemSelectDeptRequireForecastTotal(Map<String, Object> map,int pageSize,int curPage){
			
			List<Object> params = new LinkedList<Object>();
			String sql=oemSelectDeptRequireForecastSql(map);

			return dao.pageQuery(sql, params, getFunName(), pageSize, curPage);
		}
		//部门汇总查询
		private String oemSelectDeptRequireForecastSql(Map<String, Object> map){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			String dutyType=map.get("dutyType").toString() ;
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       nvl(AREA.REPORT_AMOUNT,0) REPORT_AMOUNT,\n");  
			sql.append("       nvl(DEPT.FORECAST_AMOUNT,0) FORECAST_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n");  
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and org.DUTY_TYPE = "+dutyType+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("   and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("   and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID) DEPT,\n");  
			sql.append("       (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n"); 
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("   and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("   and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID\n");  
			sql.append("\n");  
			sql.append("        ) AREA,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.PARENT_GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = DEPT.GROUP_ID\n");  
			sql.append("   AND DEPT.GROUP_ID = AREA.GROUP_ID(+)\n");  
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" UNION\n");
			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       nvl(AREA.REPORT_AMOUNT,0) REPORT_AMOUNT,\n");  
			sql.append("       nvl(DEPT.FORECAST_AMOUNT,0) FORECAST_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n");  
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and org.DUTY_TYPE = "+dutyType+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("   and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("   and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID) DEPT,\n");  
			sql.append("       (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n");  
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("   and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("   and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID\n");  
			sql.append("\n");  
			sql.append("        ) AREA,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.PARENT_GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = AREA.GROUP_ID\n");  
			sql.append("   AND DEPT.GROUP_ID(+) = AREA.GROUP_ID\n");  
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			
			sql.append(" ORDER BY SERIES_CODE");


			return sql.toString();
		}
		/*
		 *区域汇总
		 */
		public PageResult<Map<String, Object>> oemSelectAreaRequireForecastTotal(Map<String, Object> map,int pageSize,int curPage){
			
			List<Object> params = new LinkedList<Object>();
			String sql=getAreaTotalSearchSql(map);
			return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		}
		
		
		//区域汇总查询SQL
		private String getAreaTotalSearchSql(Map<String, Object> map){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String logonOrgId=(String)map.get("logonOrgId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       nvl(AREA.FORECAST_AMOUNT,0) FORECAST_AMOUNT,\n");  
			sql.append("       nvl(DEALER.REPORT_AMOUNT,0) REPORT_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_DEALER                     td\n");
			sql.append("          where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.DEALER_ID = td.DEALER_ID");
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("           and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			
			sql.append("         and f.DEALER_ID in\n");
			sql.append("               (select r.dealer_id\n");  
			sql.append("                  from TM_DEALER_ORG_RELATION r\n");  
			sql.append("                 where r.ORG_ID = "+logonOrgId+")\n");

			sql.append("         group by d.GROUP_ID) DEALER,\n");  
			sql.append("       (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n"); 
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n");  
			sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and org.ORG_ID="+logonOrgId+"\n");
			sql.append("           and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("           and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID\n");  
			sql.append("\n");  
			sql.append("        ) AREA,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.PARENT_GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = DEALER.GROUP_ID\n");  
			sql.append("   AND DEALER.GROUP_ID = AREA.GROUP_ID(+)\n");  
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" UNION\n");
			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       nvl(AREA.FORECAST_AMOUNT,0) FORECAST_AMOUNT,\n");  
			sql.append("       nvl(DEALER.REPORT_AMOUNT,0) REPORT_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_DEALER                     td\n");
			sql.append("          where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.DEALER_ID = td.DEALER_ID");
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("           and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n"); 
			sql.append("         and f.DEALER_ID in\n");
			sql.append("               (select r.dealer_id\n");  
			sql.append("                  from TM_DEALER_ORG_RELATION r\n");  
			sql.append("                 where r.ORG_ID = "+logonOrgId+")\n");
			sql.append("         group by d.GROUP_ID) DEALER,\n");  
			sql.append("       (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_ORG                        org\n");  
			sql.append("         where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.ORG_ID = org.ORG_ID\n");  
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and org.ORG_ID="+logonOrgId+"");
			params.add(logonOrgId);
			sql.append("           and f.FORECAST_YEAR = "+plan_year+"\n");  
			params.add(plan_year);
			sql.append("           and f.FORECAST_MONTH = "+plan_month+"\n");  
			params.add(plan_month);  
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("         group by d.GROUP_ID\n");  
			sql.append("\n");  
			sql.append("        ) AREA,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.PARENT_GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = AREA.GROUP_ID\n");  
			sql.append("   AND DEALER.GROUP_ID(+) = AREA.GROUP_ID\n");  
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			
			sql.append(" ORDER BY SERIES_CODE");
			
			return sql.toString();
		}
		/*
		 *dealer查询
		 */
		public PageResult<Map<String, Object>> dealerSelectRequireForecast(Map<String, Object> map,int pageSize,int curPage){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String dealerId=(String)map.get("dealerId");
			String groupCode=map.get("groupCode").toString();
			String companyId=map.get("companyId").toString();
			String areaId=map.get("areaId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       DEALER.REPORT_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_DEALER                     td\n");
			sql.append("          where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.DEALER_ID = td.DEALER_ID\n");
			sql.append("           and f.COMPANY_ID="+companyId+"\n");
			sql.append("           and f.DEALER_ID in ("+PlanUtil.createSqlStr(dealerId)+")\n");
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and f.FORECAST_YEAR = "+plan_year+"\n");  
			sql.append("           and f.FORECAST_MONTH = "+plan_month+"\n");  
			sql.append("           and f.AREA_ID = "+areaId+"\n"); 
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
			sql.append("         group by d.GROUP_ID) DEALER,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.PARENT_GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = DEALER.GROUP_ID\n");  
			if(null!=groupCode&&!"".equals(groupCode)&&!"1".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" ORDER BY SERIES_CODE");

			return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		}
		
	/*
	 * -----------------------------------------------------------下载-----------------------------------------------
	 * 
	 */
		
		/*
		 * 部门明细
		 */
		public List<Map<String, Object>> oemSelectOrgRequireForecastDown(Map<String, Object> map){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String orgCode=(String)map.get("orgCode");
			String logonOrgType=(String)map.get("logonOrgType");
			String logonOrgId=(String)map.get("logonOrgId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("select org.ORG_CODE,\n");
			sql.append("       org.ORG_NAME,\n");  
			sql.append("       f.FORECAST_YEAR || '-' || f.FORECAST_MONTH FORECAST_MONTH,\n");  
			sql.append("       g.GROUP_CODE,\n");  
			sql.append("       g.GROUP_NAME,\n");  
			sql.append("       d.GROUP_ID,\n");  
			sql.append("       sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("  from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("       TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("       TM_ORG                        org,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP        g\n");  
			sql.append(" where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("   and f.ORG_ID = org.ORG_ID\n");  
			sql.append("   and g.GROUP_ID = d.GROUP_ID\n");
			sql.append("           and f.AREA_ID = "+areaId+"\n");  
			sql.append("           and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("      -- and org.DUTY_TYPE = 10431003\n");  
			sql.append("   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
				sql.append("      and f.ORG_ID=?\n");
				params.add(logonOrgId);
			}
			sql.append("   and f.FORECAST_YEAR = ?\n");  
			params.add(plan_year);
			sql.append("   and f.FORECAST_MONTH = ?\n");  
			params.add(plan_month);
			sql.append("   and f.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
			if(null!=orgCode&&!"".equals(orgCode)){
				sql.append("           and org.org_code in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			}
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and g.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" group by d.GROUP_ID,\n");  
			sql.append("          org.ORG_CODE,\n");  
			sql.append("          org.ORG_NAME,\n");  
			sql.append("          f.FORECAST_YEAR,\n");  
			sql.append("          f.FORECAST_MONTH,\n");  
			sql.append("          g.GROUP_CODE,\n");  
			sql.append("          g.GROUP_NAME\n");  
			sql.append(" order by org.ORG_CODE, d.GROUP_ID");

			return dao.pageQuery(sql.toString(), params, getFunName());
		}
		
		/*
		 * 区域明细
		 */
		public List<Map<String, Object>> oemSelectDealerRequireForecastDown(Map<String, Object> map){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String dealerCode=(String)map.get("dealerCode");
			String logonOrgType=(String)map.get("logonOrgType");
			String logonOrgId=(String)map.get("logonOrgId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("select td.DEALER_CODE,\n");
			sql.append("       td.DEALER_SHORTNAME,\n");  
			sql.append("       f.FORECAST_YEAR || '-' || f.FORECAST_MONTH FORECAST_MONTH,\n");  
			sql.append("       g.GROUP_CODE,\n");  
			sql.append("       g.GROUP_NAME,\n");  
			sql.append("       d.GROUP_ID,\n");  
			sql.append("       sum(d.FORECAST_AMOUNT) FORECAST_AMOUNT\n");  
			sql.append("  from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("       TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("       TM_DEALER                       td,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP        g\n");  
			sql.append(" where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("   and f.DEALER_ID = td.DEALER_ID\n");  
			sql.append("   and g.GROUP_ID = d.GROUP_ID\n");  
			sql.append("   and f.AREA_ID = "+areaId+"\n");  
			sql.append("   and f.COMPANY_ID = "+companyId+"\n"); 
			sql.append("   and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
				sql.append("      and f.DEALER_ID in (select r.dealer_id\n");
				sql.append("                                    from TM_DEALER_ORG_RELATION r\n");  
				sql.append("                                   where r.ORG_ID = ?)\n");
				params.add(logonOrgId);
			}
			sql.append("   and f.FORECAST_YEAR = ?\n");  
			params.add(plan_year);
			sql.append("   and f.FORECAST_MONTH = ?\n");  
			params.add(plan_month);
			sql.append("   and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
			if(null!=dealerCode&&!"".equals(dealerCode)){
				sql.append("           and org.org_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			}
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and g.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" group by d.GROUP_ID,\n");  
			sql.append("          td.DEALER_CODE,\n");  
			sql.append("          td.DEALER_SHORTNAME,\n");  
			sql.append("          f.FORECAST_YEAR,\n");  
			sql.append("          f.FORECAST_MONTH,\n");  
			sql.append("          g.GROUP_CODE,\n");  
			sql.append("          g.GROUP_NAME\n");  
			sql.append(" order by td.DEALER_CODE, d.GROUP_ID");

			return dao.pageQuery(sql.toString(), params, getFunName());
		}
		
		/*
		 *部门汇总
		 */
		public List<Map<String, Object>> oemSelectDeptRequireForecastTotalDown(Map<String, Object> map){
			String sql=oemSelectDeptRequireForecastSql(map);

			return dao.pageQuery(sql.toString(), null, getFunName());
		}
		
		/*
		 *区域汇总
		 */
		/*public List<Map<String, Object>> oemSelectAreaRequireForecastTotalDown(Map<String, Object> map,List<Map<String,Object>> serlist){
			
			List<Object> params = new LinkedList<Object>();
			// String sql=SelectOrgForecastTotal(map,serlist);

			return dao.pageQuery(sql.toString(), params, getFunName());
		}*/
		
		/*
		 *dealer查询
		 */
		public List<Map<String, Object>> dealerSelectRequireForecastDown(Map<String, Object> map){
			String plan_year=(String)map.get("plan_year");
			String plan_month=(String)map.get("plan_month");
			String dealerId=(String)map.get("dealerId");
			String groupCode=map.get("groupCode").toString();
			String areaId=map.get("areaId").toString();
			String companyId=map.get("companyId").toString();
			
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer();

			sql.append("SELECT G1.GROUP_CODE SERIES_CODE,\n");
			sql.append("       G1.GROUP_NAME SERIES_NAME,\n");  
			sql.append("       G2.GROUP_CODE MODEL_CODE,\n");  
			sql.append("       G2.GROUP_NAME MODEL_NAME,\n");  
			sql.append("       G3.GROUP_CODE,\n");  
			sql.append("       G3.GROUP_NAME,\n");  
			sql.append("       DEALER.REPORT_AMOUNT\n");  
			sql.append("  FROM (select d.GROUP_ID, sum(d.FORECAST_AMOUNT) REPORT_AMOUNT\n");  
			sql.append("          from TT_VS_MONTHLY_FORECAST        f,\n");  
			sql.append("               TT_VS_MONTHLY_FORECAST_DETAIL d,\n");  
			sql.append("               TM_DEALER                     td\n");
			sql.append("          where f.FORECAST_ID = d.FORECAST_ID\n");  
			sql.append("           and f.DEALER_ID = td.DEALER_ID\n");
			sql.append("           and f.COMPANY_ID="+companyId+"\n");
			sql.append("           and f.DEALER_ID in ("+PlanUtil.createSqlStr(dealerId)+")\n");
			sql.append("           and f.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
			sql.append("           and f.FORECAST_YEAR = ?\n");  
			params.add(plan_year);
			sql.append("           and f.FORECAST_MONTH = ?\n");  
			params.add(plan_month);
			sql.append("           and f.AREA_ID = "+areaId+"\n"); 
			sql.append("           and f.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
			sql.append("         group by d.GROUP_ID) DEALER,\n");  

			sql.append("       TM_VHCL_MATERIAL_GROUP G1,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G2,\n");  
			sql.append("       TM_VHCL_MATERIAL_GROUP G3\n");  
			sql.append(" WHERE G1.GROUP_ID = G2.PARENT_GROUP_ID\n");  
			sql.append("   AND G2.GROUP_ID = G3.GROUP_ID\n");  
			sql.append("   AND G3.GROUP_ID = DEALER.GROUP_ID\n");  
			if(null!=groupCode&&!"".equals(groupCode)){
				sql.append("           and G3.GROUP_CODE in ("+PlanUtil.createSqlStr(groupCode)+")\n");  
			}
			sql.append(" ORDER BY SERIES_CODE");

			return dao.pageQuery(sql.toString(), params, getFunName());
		}
		
		/*
		 * 查询前三月的参考信息
		 */
		public PageResult<Map<String, Object>> selectHistoryMonth(String duty,String orgId,String groupId,List<Map<String, Object>> list,int pageSize,int curPage){
			List<Object> params = new LinkedList<Object>();
			StringBuffer sql=new StringBuffer("");


			sql.append("SELECT GRO.GROUP_ID, --配置ID\n");
			sql.append("       GRO.GROUP_CODE,--配置代码\n");  
			sql.append("       GRO.GROUP_NAME,--配置名称\n");  
			sql.append("       NVL(FORE.FORECAST_AMOUNT, 0) AS FORECAST_AMOUNT, --前三个月平均预测数量\n");  
			sql.append("       NVL(ORD.ORDER_AMOUNT, 0) AS ORDER_AMOUNT, --前三月平均订单数量\n");  
			sql.append("       NVL(ORD.MATCH_AMOUNT, 0) AS MATCH_AMOUNT, --前三月平均实销量\n");  
			sql.append("       NVL(VEH.VEHICLE_AMOUNT, 0) AS VEHICLE_AMOUNT --有效库存数量\n");  
			sql.append("  FROM (SELECT TVMG.GROUP_CODE, TVMG.GROUP_ID, TVMG.GROUP_NAME\n");  
			sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
			sql.append("         WHERE TVMG.GROUP_ID = "+groupId+"--换成配置ID\n");  
			sql.append("         ) GRO\n");  
			sql.append("  LEFT JOIN (SELECT TVMGR.GROUP_ID,\n");  
			sql.append("                    ROUND(NVL(SUM(TVOD.ORDER_AMOUNT), 0) / 3, 0) AS ORDER_AMOUNT, --前三月平均订单数量\n");  
			sql.append("                    ROUND(NVL(SUM(TVOD.MATCH_AMOUNT), 0) / 3, 0) AS MATCH_AMOUNT --前三月平均实销量\n");  
			sql.append("               FROM TT_VS_ORDER              TVO,\n");  
			sql.append("                    TT_VS_ORDER_DETAIL       TVOD,\n");  
			sql.append("                    TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
			sql.append("              WHERE TVO.ORDER_ID = TVOD.ORDER_ID\n");  
			sql.append("                AND TVOD.MATERIAL_ID = TVMGR.MATERIAL_ID\n");  
			sql.append("                AND TVO.ORDER_STATUS IN("+Constant.ORDER_STATUS_03+","+Constant.ORDER_STATUS_05+") --只要审核通过的和已提报的\n");  
			if(null!=orgId&&!"".equals(orgId)&&duty.equals(Constant.DUTY_TYPE_LARGEREGION.toString())){
				sql.append("                AND TVO.ORDER_ORG_ID in (select r.dealer_id from TM_DEALER_ORG_RELATION r where r.ORG_ID = "+orgId+")\n");  
			}
			sql.append("                AND (\n");  
			for(int i=0;i<list.size();i++){
				Map<String, Object> map=list.get(i);
				sql.append("(TVO.ORDER_YEAR = "+map.get("YEAR")+" AND TVO.ORDER_MONTH = "+map.get("MONTH")+") OR\n");
			}
			sql.delete(sql.lastIndexOf("OR"), sql.length());
			sql.append("                    ) --取当前的月的前三个月和年一起考虑\n");  
			sql.append("              GROUP BY TVMGR.GROUP_ID) ORD ON GRO.GROUP_ID = ORD.GROUP_ID\n");  
			sql.append("  LEFT JOIN (SELECT TVMFD.GROUP_ID,\n");  
			sql.append("                    ROUND(NVL(SUM(TVMFD.FORECAST_AMOUNT), 0) / 3, 0) AS FORECAST_AMOUNT --预测数量\n");  
			sql.append("               FROM TT_VS_MONTHLY_FORECAST        TVMF,\n");  
			sql.append("                    TT_VS_MONTHLY_FORECAST_DETAIL TVMFD\n");  
			sql.append("              WHERE TVMF.FORECAST_ID = TVMFD.FORECAST_ID\n");  
			if(null!=orgId&&!"".equals(orgId)){
				sql.append("                AND TVMF.ORG_ID in ("+PlanUtil.createSqlStr(orgId)+") --换成dealer_id\n");
			}
			sql.append("                AND TVMF.ORG_TYPE="+Constant.ORG_TYPE_OEM+"\n");  
			sql.append("                AND TVMF.STATUS ="+Constant.FORECAST_STATUS_CONFIRM+" --取已提交的预测\n");  
			sql.append("                AND (\n");  
			for(int i=0;i<list.size();i++){
				Map<String, Object> map=list.get(i);
				sql.append("(TVMF.FORECAST_YEAR = "+map.get("YEAR")+" AND TVMF.FORECAST_MONTH = "+map.get("MONTH")+") OR\n");
			}
			sql.delete(sql.lastIndexOf("OR"), sql.length());
			sql.append("                   ) --取当前的月的前三个月和年一起考虑\n");  
			sql.append("              GROUP BY TVMFD.GROUP_ID) FORE ON GRO.GROUP_ID = FORE.GROUP_ID\n");  
			sql.append("\n");  
			sql.append("  LEFT JOIN (SELECT TVMGR.GROUP_ID, COUNT(TV.VEHICLE_ID) AS VEHICLE_AMOUNT\n");  
			sql.append("               FROM TM_VEHICLE TV, TM_VHCL_MATERIAL_GROUP_R TVMGR\n");  
			sql.append("              WHERE TV.MATERIAL_ID = TVMGR.MATERIAL_ID\n");  
			sql.append("                AND TV.ORG_TYPE IS NULL\n");  
			sql.append("                AND TV.ORG_ID IS NULL\n");  
			sql.append("                AND TV.DEALER_ID IS NULL\n");  
			sql.append("                AND TV.LOCK_STATUS = "+Constant.LOCK_STATUS_01+"\n");  
			sql.append("                AND TV.LIFE_CYCLE = "+Constant.VEHICLE_LIFE_02+"\n");  
			sql.append("              GROUP BY TVMGR.GROUP_ID) VEH ON GRO.GROUP_ID = VEH.GROUP_ID");


			PageResult<Map<String, Object>> ps = pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
			return ps;
		}	
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}
	// 车厂汇总
	public PageResult<Map<String, Object>> oemSelectDealerMonthForecastTotal(Map<String, Object> map,List<Map<String, Object>> serlist,int pageSize,int curPage){
		String forecast_year=(String)map.get("forecast_year");
		String forecast_month=(String)map.get("forecast_month");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.DEALER_CODE,\n");  
		sql.append("       YEARLY.DEALER_SHORTNAME,\n");
		
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.GROUP_ID,"+sermap.get("GROUP_ID")+",YEARLY.FORE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       nvl(sum(YEARLY.FORE_AMOUNT),0) FA,\n");
		sql.append("       YEARLY.DEALER_ID\n");  
		sql.append("  FROM (SELECT TVMF.DEALER_ID,\n"); 
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");
		sql.append("               tvmg1.GROUP_ID,\n");  
		sql.append("               nvl(SUM(TVMFD.FORECAST_AMOUNT),0) AS FORE_AMOUNT\n");  
		sql.append("          FROM tt_vs_monthly_forecast    tvmf,\n");  
		sql.append("               tt_vs_monthly_forecast_detail  tvmfd,\n");  
		sql.append("               TM_DEALER                TD,\n"); 
		sql.append("				tm_vhcl_material_group tvmg1,\n");
		sql.append("				tm_vhcl_material_group tvmg2,\n");  
		sql.append("				tm_vhcl_material_group tvmg3\n");

		
		sql.append("         WHERE TVMF.FORECAST_ID = TVMFD.FORECAST_ID\n");  
		sql.append("           AND TVMF.DEALER_ID = TD.DEALER_ID\n");
		sql.append("			and tvmfd.group_id = tvmg3.group_id\n");
		sql.append("			and tvmg3.parent_group_id = tvmg2.group_id\n");  
		sql.append("			and tvmg2.parent_group_id = tvmg1.group_id\n");

		sql.append("           AND TVMF.AREA_ID="+areaId+"\n");
		sql.append("           AND TVMF.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVMF.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("          and TVMF.dealer_id in\n" );
			sql.append("       (select m.dealer_id\n" );
			sql.append("          from tm_dealer m\n" );
			sql.append("        CONNECT BY PRIOR m.dealer_id = m.parent_dealer_d\n" );
			sql.append("         START WITH m.status="+Constant.STATUS_ENABLE+" and m.dealer_id in\n" );
			sql.append("                    (select rel.dealer_id\n" );
			sql.append("                       from tm_dealer_org_relation rel\n" );
			sql.append("                      where rel.org_id ="+logonOrgId+"))");
		}
		sql.append("           and TVMF.FORECAST_YEAR = "+forecast_year+"\n");  
		sql.append("           and TVMF.FORECAST_MONTH = "+forecast_month+"\n");  
		sql.append("           and TVMF.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
		}
		
		sql.append("         GROUP BY TVMF.DEALER_ID,\n");  
		sql.append("                  tvmg1.GROUP_ID,\n");  
		sql.append("                  TD.DEALER_CODE,\n");  
		sql.append("                  TD.DEALER_SHORTNAME) YEARLY\n"); 
		sql.append(" GROUP BY DEALER_ID, YEARLY.DEALER_CODE, YEARLY.DEALER_SHORTNAME \n");  
		sql.append(" order by dealer_id");

		return dao.pageQuery(sql.toString(), null, getFunName(), pageSize, curPage);
	}
	
	public PageResult<Map<String, Object>> oemSelectOrgForecastTotal(Map<String, Object> map,List<Map<String, Object>> serlist,int pageSize,int curPage){
		String forecast_year=(String)map.get("forecast_year");
		String forecast_month=(String)map.get("forecast_month");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT YEARLY.ORG_ID,\n");
		sql.append("       YEARLY.ORG_CODE,\n");  
		sql.append("       YEARLY.ORG_NAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.GROUP_ID,"+sermap.get("GROUP_ID")+",YEARLY.FORE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       nvl(sum(yearly.FORE_AMOUNT), 0) FA \n");  
		sql.append("  FROM (SELECT TOR.ORG_ID,\n");  
		sql.append("               TOR.ORG_CODE,\n");  
		sql.append("               TOR.ORG_NAME,\n"); 
		sql.append("               tvmg1.GROUP_ID,\n");  
		sql.append("               nvl(SUM(tvmfd.forecast_AMOUNT), 0) AS FORE_AMOUNT\n");  
		sql.append("          FROM tt_vs_monthly_forecast    tvmf,\n");  
		sql.append("               tt_vs_monthly_forecast_detail tvmfd,\n");   
		sql.append("               TM_ORG                    TOR,\n");  
		sql.append("		   tm_vhcl_material_group        tvmg1,\n");
		sql.append("		   tm_vhcl_material_group        tvmg2,\n");  
		sql.append("		   tm_vhcl_material_group        tvmg3\n");
		sql.append("         WHERE tvmf.forecast_ID = tvmfd.forecast_ID\n");  
		sql.append("		and tvmfd.group_id = tvmg3.group_id\n");
		sql.append("		and tvmg3.parent_group_id = tvmg2.group_id\n");  
		sql.append("		and tvmg2.parent_group_id = tvmg1.group_id\n");
		sql.append("		and tvmf.org_id = tor.org_id\n");  
		sql.append("           AND tvmf.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND tvmf.AREA_ID = "+areaId+"\n");  
		sql.append("           AND tvmf.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("          and tvmf.org_id in\n" );
			sql.append("       (select m.org_id\n" );
			sql.append("          from tm_org m\n" );
			sql.append("        CONNECT BY PRIOR m.org_id = m.parent_org_id\n" );
			sql.append("         START WITH m.org_id ="+logonOrgId+")\n" );
		}
		sql.append("           and TVMF.FORECAST_YEAR = "+forecast_year+"\n");  
		sql.append("           and TVMF.FORECAST_MONTH = "+forecast_month+"\n");  
		sql.append("           and TVMF.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n"); 
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("       and tor.org_code in ("+PlanUtil.createSqlStr(orgCode)+")");
		}
		sql.append("         GROUP BY tvmg1.GROUP_ID,\n");  
		sql.append("                  TOR.ORG_ID,\n");  
		sql.append("                  TOR.ORG_CODE,\n");  
		sql.append("                  TOR.ORG_NAME) YEARLY\n");  
		sql.append(" GROUP BY YEARLY.ORG_ID, YEARLY.ORG_CODE, YEARLY.ORG_NAME \n");  
		sql.append(" order by YEARLY.ORG_ID");

		/*return factory.pageQuery(sql.toString(), null,new DAOCallback<Map<String, Object>>() {
			public Map<String, Object> wrapper(ResultSet rs, int i) {
				Map<String,Object> map = new HashMap<String,Object>() ;

				try {
				map.put("ORG_ID", rs.getInt("ORG_ID")) ;
				System.out.println("+++++++++"+rs.getString("ORG_NAME"));
				map.put("ORG_CODE", rs.getString("ORG_CODE")) ;
				map.put("ORG_NAME", rs.getString("ORG_NAME")) ;
				map.put("S0", rs.getInt("S0")) ;
				map.put("S1", rs.getInt("S1")) ;
				map.put("S2", rs.getInt("S2")) ;
				map.put("S3", rs.getInt("S3")) ;
				map.put("SS", rs.getInt("SS")) ;
			} catch(Exception e) {
			}

			return map ;
			}
		}
, pageSize, curPage) ;*/
		return dao.pageQuery(sql.toString(), null, getFunName()+serlist.size(), pageSize, curPage);
	}
	
	// 区域汇总查询下载
	public List<Map<String, Object>> SelectOrgForecastTotal(Map<String, Object> map,List<Map<String, Object>> serlist){
		String forecast_year=(String)map.get("plan_year");
		String forecast_month=(String)map.get("plan_month");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("SELECT YEARLY.ORG_ID,\n");
		sql.append("       YEARLY.ORG_CODE,\n");  
		sql.append("       YEARLY.ORG_NAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.GROUP_ID,"+sermap.get("GROUP_ID")+",YEARLY.FORE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       nvl(sum(yearly.FORE_AMOUNT), 0) FA \n");  
		sql.append("  FROM (SELECT TOR.ORG_ID,\n");  
		sql.append("               TOR.ORG_CODE,\n");  
		sql.append("               TOR.ORG_NAME,\n"); 
		sql.append("               tvmg1.GROUP_ID,\n");  
		sql.append("               nvl(SUM(tvmfd.forecast_AMOUNT), 0) AS FORE_AMOUNT\n");  
		sql.append("          FROM tt_vs_monthly_forecast    tvmf,\n");  
		sql.append("               tt_vs_monthly_forecast_detail tvmfd,\n");   
		sql.append("               TM_ORG                    TOR,\n");  
		sql.append("		   tm_vhcl_material_group        tvmg1,\n");
		sql.append("		   tm_vhcl_material_group        tvmg2,\n");  
		sql.append("		   tm_vhcl_material_group        tvmg3\n");
		sql.append("         WHERE tvmf.forecast_ID = tvmfd.forecast_ID\n");  
		sql.append("		and tvmfd.group_id = tvmg3.group_id\n");
		sql.append("		and tvmg3.parent_group_id = tvmg2.group_id\n");  
		sql.append("		and tvmg2.parent_group_id = tvmg1.group_id\n");
		sql.append("		and tvmf.org_id = tor.org_id\n");  
		sql.append("           AND tvmf.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND tvmf.AREA_ID = "+areaId+"\n");  
		sql.append("           AND tvmf.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("          and tvmf.org_id in\n" );
			sql.append("       (select m.org_id\n" );
			sql.append("          from tm_org m\n" );
			sql.append("        CONNECT BY PRIOR m.org_id = m.parent_org_id\n" );
			sql.append("         START WITH m.org_id ="+logonOrgId+")\n" );
		}
		sql.append("           and TVMF.FORECAST_YEAR = "+forecast_year+"\n");  
		sql.append("           and TVMF.FORECAST_MONTH = "+forecast_month+"\n");  
		sql.append("           and TVMF.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n"); 
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("       and tor.org_code in ("+PlanUtil.createSqlStr(orgCode)+")");
		}
		sql.append("         GROUP BY tvmg1.GROUP_ID,\n");  
		sql.append("                  TOR.ORG_ID,\n");  
		sql.append("                  TOR.ORG_CODE,\n");  
		sql.append("                  TOR.ORG_NAME) YEARLY\n");  
		sql.append(" GROUP BY YEARLY.ORG_ID, YEARLY.ORG_CODE, YEARLY.ORG_NAME \n");  
		sql.append(" order by YEARLY.ORG_ID");

		return dao.pageQuery(sql.toString(), null, getFunName());
	}
	
	//经销商汇总查询下载
	// 车厂汇总
	public List<Map<String, Object>> SelectDLRForecastTotal(Map<String, Object> map,List<Map<String, Object>> serlist){
		String forecast_year=(String)map.get("plan_year");
		String forecast_month=(String)map.get("plan_month");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.DEALER_CODE,\n");  
		sql.append("       YEARLY.DEALER_SHORTNAME,\n");
		
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.GROUP_ID,"+sermap.get("GROUP_ID")+",YEARLY.FORE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       nvl(sum(YEARLY.FORE_AMOUNT),0) FA,\n");
		sql.append("       YEARLY.DEALER_ID\n");  
		sql.append("  FROM (SELECT TVMF.DEALER_ID,\n"); 
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");
		sql.append("               tvmg1.GROUP_ID,\n");  
		sql.append("               nvl(SUM(TVMFD.FORECAST_AMOUNT),0) AS FORE_AMOUNT\n");  
		sql.append("          FROM tt_vs_monthly_forecast    tvmf,\n");  
		sql.append("               tt_vs_monthly_forecast_detail  tvmfd,\n");  
		sql.append("               TM_DEALER                TD,\n"); 
		sql.append("				tm_vhcl_material_group tvmg1,\n");
		sql.append("				tm_vhcl_material_group tvmg2,\n");  
		sql.append("				tm_vhcl_material_group tvmg3\n");

		
		sql.append("         WHERE TVMF.FORECAST_ID = TVMFD.FORECAST_ID\n");  
		sql.append("           AND TVMF.DEALER_ID = TD.DEALER_ID\n");
		sql.append("			and tvmfd.group_id = tvmg3.group_id\n");
		sql.append("			and tvmg3.parent_group_id = tvmg2.group_id\n");  
		sql.append("			and tvmg2.parent_group_id = tvmg1.group_id\n");

		sql.append("           AND TVMF.AREA_ID="+areaId+"\n");
		sql.append("           AND TVMF.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVMF.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("          and TVMF.dealer_id in\n" );
			sql.append("       (select m.dealer_id\n" );
			sql.append("          from tm_dealer m\n" );
			sql.append("        CONNECT BY PRIOR m.dealer_id = m.parent_dealer_d\n" );
			sql.append("         START WITH m.status="+Constant.STATUS_ENABLE+" and m.dealer_id in\n" );
			sql.append("                    (select rel.dealer_id\n" );
			sql.append("                       from tm_dealer_org_relation rel\n" );
			sql.append("                      where rel.org_id ="+logonOrgId+"))");
		}
		sql.append("           and TVMF.FORECAST_YEAR = "+forecast_year+"\n");  
		sql.append("           and TVMF.FORECAST_MONTH = "+forecast_month+"\n");  
		sql.append("           and TVMF.STATUS = "+Constant.FORECAST_STATUS_CONFIRM+"\n");  
		
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
		}
		
		sql.append("         GROUP BY TVMF.DEALER_ID,\n");  
		sql.append("                  tvmg1.GROUP_ID,\n");  
		sql.append("                  TD.DEALER_CODE,\n");  
		sql.append("                  TD.DEALER_SHORTNAME) YEARLY\n"); 
		sql.append(" GROUP BY DEALER_ID, YEARLY.DEALER_CODE, YEARLY.DEALER_SHORTNAME \n");  
		sql.append(" order by dealer_id");

		return dao.pageQuery(sql.toString(), null, getFunName());
	}
}
